/*EPO claims need to be classified into claims referencing other claims and independent claims*/
/* whenever the word claim(s) appear, the claim should be dependent (referencing another claim)*/



/***** Classification implementation EPO ******************************************************************************/

ALTER TABLE epo_01_claims_from_ep_xml ADD COLUMN depending_claim_ BOOLEAN;


UPDATE epo_01_claims_from_ep_xml

SET depending_claim_ = CASE WHEN (

        claim_text_ LIKE '% claims %'
     OR claim_text_ LIKE '% claim %'
     OR claim_text_ LIKE '% claims'
     OR claim_text_ LIKE '% claim'
      ) THEN TRUE

     ELSE FALSE
     END

WHERE claim_lang = 'en'
;



UPDATE epo_01_claims_from_ep_xml

SET depending_claim_ = CASE WHEN (

         claim_text_ LIKE '%anspruch %'
      OR claim_text_ LIKE '%anspruche %'
      OR claim_text_ LIKE '%anspruech %'
      OR claim_text_ LIKE '%ansprueche %'
      OR claim_text_ LIKE '%anspruchen %'
      OR claim_text_ LIKE '%anspruechen %'
      OR claim_text_ LIKE '%anspruch'
      OR claim_text_ LIKE '%anspruche'
      OR claim_text_ LIKE '%anspruech'
      OR claim_text_ LIKE '%ansprueche'
      OR claim_text_ LIKE '%anspruchen'
      OR claim_text_ LIKE '%anspruechen'
      ) THEN TRUE

     ELSE FALSE
     END

WHERE claim_lang = 'de'
;



UPDATE epo_01_claims_from_ep_xml

SET depending_claim_ = CASE WHEN (

         claim_text_ LIKE '% revendication %'
      OR claim_text_ LIKE '% revendications %'
      OR claim_text_ LIKE '% revendication'
      OR claim_text_ LIKE '% revendications'
      ) THEN TRUE

     ELSE FALSE
     END

WHERE claim_lang = 'fr'
;



/***** Classification implementation USPTO ******************************************************************************/

ALTER TABLE epo_02_claims_from_us_csv ADD COLUMN depending_claim_ BOOLEAN;


UPDATE epo_02_claims_from_us_csv

SET depending_claim_ = CASE WHEN (

        claim_text_ LIKE '% claims %'
     OR claim_text_ LIKE '% claim %'
     OR claim_text_ LIKE '% claims'
     OR claim_text_ LIKE '% claim'

      ) THEN TRUE

     ELSE FALSE
     END

;



/***** set 1st claim to independent (this cleans cases where a patent only has dependent claims *****************************************************************************/

CREATE INDEX epo_01_claims_from_ep_xml_claim_number_corrected ON epo_01_claims_from_ep_xml(claim_number_corrected);
CREATE INDEX epo_02_claims_from_us_csv_claim_no ON epo_02_claims_from_us_csv(claim_no);

UPDATE epo_01_claims_from_ep_xml SET
depending_claim_ = FALSE WHERE claim_number_corrected = 1;

UPDATE epo_02_claims_from_us_csv SET
depending_claim_ = FALSE WHERE claim_no = 1;


------------------------------------------------------------------------------------------------------------------------------------------------------------------------











